import io
from urllib.parse import quote

import xlwt
from django.db import connection
from django.http import HttpRequest, HttpResponse, JsonResponse
from django.shortcuts import redirect
from django.utils import timezone

from search.models import Record


def index(request: HttpRequest) -> HttpResponse:
    return redirect('/static/html/index.html')


# def search(request: HttpRequest) -> HttpResponse:
#     page = int(request.GET.get('page', '1'))
#     size = int(request.GET.get('size', '5'))
#     # 一对一、多对一外键关联可以通过QuerySet对象的select_related('关联对象')解决1+N查询问题
#     # 多对多外键关联可以通过QuerySet对象的prefetch_related('关联对象')解决1+N查询问题
#     # 可以通过QuerySet对象的only方法指定哪些字段需要投影
#     # 可以通过QuerySet对象的defer方法指定哪些字段是不需要投影的
#     queryset = Record.objects.filter(is_deleted=False)\
#         .defer('is_deleted', 'deleted_time', 'updated_time')\
#         .select_related('car')
#     carinfo = request.POST.get('carinfo', '')
#     if carinfo:
#         carinfo = re.sub(r'\s', '', carinfo)
#         queryset = queryset.filter(
#             Q(car__carno__istartswith=carinfo) | Q(car__owner__icontains=carinfo)
#         )
#     count = queryset.count()
#     queryset = queryset.order_by('-makedate')[(page - 1) * size:page * size]
#     total_page = (count - 1) // size + 1
#     return render(request, 'index.html', {
#         'records': queryset,
#         'current_page': page,
#         'page_size': size,
#         'total_page': total_page,
#         'has_prev': page > 1,
#         'has_next': page < total_page,
#         'prev_page': page - 1,
#         'next_page': page + 1,
#         'carinfo': carinfo
#     })


def handle_record(request: HttpRequest) -> HttpResponse:
    try:
        rno = int(request.GET.get('rno'))
    except ValueError:
        data = {'code': 30002, 'mesg': '违章记录编号无效'}
    else:
        record = Record.objects.filter(no=rno, dealt=False).first()
        if record:
            record.dealt = True
            record.updated_time = timezone.now()
            record.save()
            data = {'code': 30000, 'mesg': '受理成功'}
        else:
            data = {'code': 30001, 'mesg': '受理失败'}
    return JsonResponse(data)


def delete_record(request: HttpRequest) -> HttpResponse:
    try:
        rno = int(request.GET.get('rno'))
    except ValueError:
        data = {'code': 40002, 'mesg': '违章记录编号无效'}
    else:
        record = Record.objects.filter(no=rno, is_deleted=False).first()
        if record:
            if record.dealt:
                record.is_deleted = True
                record.deleted_time = timezone.now()
                record.save()
                data = {'code': 40000, 'mesg': '删除成功'}
            else:
                data = {'code': 40003, 'mesg': '不能删除尚未受理的违章记录'}
        else:
            data = {'code': 40001, 'mesg': '删除失败'}
    return JsonResponse(data)


def export_excel(request: HttpRequest) -> HttpResponse:
    queryset = Record.objects.filter(is_deleted=False) \
        .defer('is_deleted', 'deleted_time', 'updated_time') \
        .select_related('car').order_by('no')
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('违章记录表')
    titles = ('编号', '车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '是否受理')
    # style = xlwt.easyxf('font: height 720, color-index red')
    for col_index, title in enumerate(titles):
        sheet.write(0, col_index, title)
    for row_index, record in enumerate(queryset):
        sheet.write(row_index + 1, 0, record.no)
        sheet.write(row_index + 1, 1, record.car.carno)
        sheet.write(row_index + 1, 2, record.car.owner)
        sheet.write(row_index + 1, 3, record.reason)
        sheet.write(row_index + 1, 4, record.makedate.strftime('%Y-%m-%d'))
        sheet.write(row_index + 1, 5, record.punish)
        sheet.write(row_index + 1, 6, '已受理' if record.dealt else '未受理')
    buffer = io.BytesIO()
    # str - 字符串 - 'hello' ---> io.StringIO
    # bytes - 字节串 - b'\xff\xe0\x9a' ---> io.BytesIO ---> 内存区域
    wb.save(buffer)
    # 创建HTTP响应对象并指定MIME类型（给浏览器的内容的类型）
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    # 将中文文件名处理成百分号编码
    filename = quote('违章记录汇总统计表.xls')
    # 设置HTTP响应头（设置下载文件并指定文件名）
    # resp['content-type'] = 'application/vnd.ms-excel'
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp


def get_bar_data(request: HttpRequest) -> HttpResponse:
    xdata, ydata = [], []
    with connection.cursor() as cursor:
        cursor.execute('select carno, ifnull(total, 0) as total '
                       ' from tb_car t1 left outer join '
                       ' (select car_id, count(no) as total '
                       ' from tb_record group by car_id) t2 '
                       ' on t1.no=t2.car_id')
        for row in cursor.fetchall():
            xdata.append(row[0])
            ydata.append(row[1])
    return JsonResponse({'xdata': xdata, 'ydata': ydata})
